import pymysql


# 插入新的停车预约 参数：车牌，预约时长      更新appointment_record表
def add_appointment(car_plate, duration=None):
    if not query_appointment_by_plate(car_plate):
        conn = pymysql.connect(
            host="cdb-4o4cwcko.bj.tencentcdb.com",
            port=10083,
            db="car",
            user="root",
            password="njlj2666",
            charset="utf8"
        )
        cls = conn.cursor()
        sql = "insert into appointment_record (car_plate, appointment_duration) value(%s,%s)"
        try:
            cls.execute(sql, [car_plate, duration])
            conn.commit()
        except:
            conn.rollback()
        finally:
            conn.close()
    else:
        print('%s有尚未完成的预约' % car_plate)


# 按车牌查询尚未完成的预约记录 返回值：T:存在未完成的预约 F：没有未完成的预约
def query_appointment_by_plate(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from appointment_record where car_plate=%s and state=0"
    cls.execute(sql, [car_plate])
    result = cls.fetchone()
    conn.close()
    if result is not None:
        return True
    elif result is None:
        return False


def query_appointment_by_plate1(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from appointment_record where car_plate=%s and state=0"
    cls.execute(sql, [car_plate])
    result = cls.fetchone()
    conn.close()
    if result is not None:
        return True,result
    elif result is None:
        return False,result


# 完成预约，更新预约状态至‘已完成’
def complete_appointment(car_plate):
    if query_appointment_by_plate(car_plate):
        conn = pymysql.connect(
            host="cdb-4o4cwcko.bj.tencentcdb.com",
            port=10083,
            db="car",
            user="root",
            password="njlj2666",
            charset="utf8"
        )
        try:
            cls = conn.cursor()
            sql = "update appointment_record set state=1 where car_plate=%s and state=0"
            cls.execute(sql, [car_plate])
            conn.commit()
        except:
            conn.rollback()
        finally:
            conn.close()
    else:
        print('未查询到%s预约' % car_plate)


# 查询未完成的预约记录数
def query_appointment_count():
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select count(*) from appointment_record where state=0"
    cls.execute(sql)
    result = cls.fetchone()
    conn.close()
    return result[0]


# 查询所有未完成的预约 返回list所有未完成预约的DDL和id
def query_appointment_all_ddl():
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from appointment_record where state=0"
    cls.execute(sql)
    result = cls.fetchall()
    conn.close()

    appoinment_list = []
    for i in range(len(result)):
        ddl =result[i][2]+result[i][3]
        appoinment = {'ID': result[i][0], '车牌': result[i][1], '预约期限': ddl.strftime('%Y-%m-%d %H:%M:%S')}
        appoinment_list.append(appoinment)

    return appoinment_list


if __name__ == '__main__':
    add_appointment('浙J1573Q', '5:0:0')
    print(query_appointment_all_ddl())
